

// -----------------------------------------------------------------------------

#include "stdafx.h"
#include "Win32.MemoryItem.h"
#include "Win32.DataObjects.Submissions.h"
#include "Win32.Data.Odbc.h"
#include "Win32.Diagnostics.Result.h"

#define MAX_IMAGE_SIZE (1024*3000)

namespace Kif
{
namespace Win32
{
namespace DataObjects
{

/// <summary>Given an Error Number prepare given Result</summary>
/// <param name="errorNo">Error Number</param>
/// <param name="result">Result structure/class to fill in based on given
/// error number</param>
/// <returns>true is returns if done</returns>

TCHAR *Submission::GetErrorMessage(long errorNo)
{
   TCHAR *mess = _T("");
   switch(errorNo)
   {
      case SubmissionError_Success:
         mess = _T("Success");
         break;
      case SubmissionError_InvalidSubmissionId:
         mess = _T("Invalid Submission Id.");
         break;
      case SubmissionError_FailedToRegisterSubmission:
         mess = _T("Failed to register submission");
         break;
      case SubmissionError_SubmissionRegistryAlreadyExists:
         mess = _T("Submission registry already exists");
         break;
      case SubmissionError_FailedToUpdateSubmssionStatus:
         mess = _T("Failed to update submission status");
         break;
      case SubmissionError_FailedToInsertStatusAndMessage:
         mess = _T("Failed to insert status and message");
         break;
      case SubmissionError_SubmissionNotFound:
         mess = _T("Submission not found");
         break;
      case SubmissionError_FailedToRegisterMedia:
         mess = _T("Failed to register media");
         break;
      case SubmissionError_BadFileFormatUnreadable:
         mess = _T("Bad File Format, Unreadable File");
         break;
      case SubmissionError_NotEnoughSpaceForPSID:
         mess = _T("Not enough space for PersonStateId");
         break;
      case SubmissionError_FailedReplacingPSIDFile:
         mess = _T("Failed to write submission file while replacing PSID");
         break;
      case SubmissionError_PSIDNotFound:
         mess = _T("PSID/Reference Id not found in submission file");
         break;
      case SubmissionError_IncidentIdNotFound:
         mess = _T("Incident Id (Complain No.) not found in submission file");
         break;
      case SubmissionError_PSIDNotRelatedToIncidentId:
         mess = _T("Person State Id not related to Incident");
         break;
      case SubmissionError_NoneOneOrManyReferencesFound:
         mess = _T("None, one or many references found");
         break;
      case SubmissionError_FailedToValidateSubmission:
         mess = _T("Failed to Validate Submission");
         break;
      case SubmissionError_FailedReplacingFileNotEnoughSpace:
         mess = _T("Failed replacing tag data since there was not enough space");
         break;
      case SubmissionError_TagToReplaceNotFound:
         mess = _T("Failed replacing tag since it was not found");
         break;
      case SubmissionError_InvalidIncidentId:
         mess = _T("Invalid Incident Id");
         break;
      case SubmissionError_DatabaseRequestFailed:
         mess = _T("Database Request Failed");
         break;
      case SubmissionError_FailedRetrievingSubmissionId:
         mess = _T("Failed Retrieving Submission Id");
         break;
      default:
         mess = NULL;
         break;
   }

   return(mess);
}  // end of GetErrorMessage

bool Submission::GetErrorMessage(
   long errorNo, TCHAR *location,
   Kif::Win32::Diagnostics::Result &result)
{
   bool done = true;

   result.LocalErrorNo = errorNo;
   TCHAR *mess = GetErrorMessage(errorNo);

   return(true);
}  // end of GetErrorMessage

/// <summary>Connect to database using given datasource</summary>
/// <param name="DataSource">Data source</param>
/// <returns>a pointer to a Result is returned. the caller is responsable for
/// disposing the result using the "delete" operator</returns>

bool SubmissionDbConnection::Connect(
   Kif::Win32::Data::DataSourceInfo &dataSource)
{
   Connected = false;
   LastResult.Clear();

   // prepare database connection
   Connection.Database.Verbose = Kif::Win32::Diagnostics::Verbose_Trace;
   if (!Connection.Connect(dataSource))
   {
      LastResult.ErrorNo = ERROR_CONNECTION_REFUSED;
      Connected = false;
   }
   else
      Connected = true;
   return(Connected);
}  // end of Connect


/// <summary>Get a session Id.</summary>
/// <param name="connection">database connection</param>
/// <param name="outSessionId">Outputed Session Id</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::Result *Submission::GetSessionId(
   SubmissionDbConnection &dbConnection,
   Kif::Win32::String &outSessionId)
{
   // prepare result
   Kif::Win32::Diagnostics::Result *result = &dbConnection.LastResult;
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionInquireSessionId (?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   TCHAR  sessionId[] = _T("");
   long   rval = 0;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   TCHAR sessId[Submission::SubmissionIdLength+1];
   sessId[0] = NULL;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pOutSessionId =
      cmd.Parameters.Add(_T("OutSessionId"),sessId, 24);
   pOutSessionId->Direction = Kif::Win32::MemoryParamDirection_Output;

   bool done = cmd.ExecuteNonQuery();
   rval = done ? pReturnValue->AsLong() : SubmissionError_DatabaseRequestFailed;
   if ((rval != 0) && (result != NULL))
   {
      GetErrorMessage(rval,_T("Submission::GetSessionId"),*result);
   }
   else
   {
      wchar_t *refId = pOutSessionId->AsWideString();
      outSessionId.Copy(refId);
      outSessionId.Trim();
   }

   // dipose of resources
   cmd.Dispose();

   return(result);
}  // end of GetSessionId

/// <summary>Get the submission id of tracking-id.</summary>
/// <param name="connection">database connection</param>
/// <param name="trackingId">Tracking id</param>
/// <param name="outSubmissionId">Outputed Submission Id</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::Result *Submission::GetSubmissionId(
   SubmissionDbConnection &dbConnection,
   Kif::Win32::String &sessionId,
   Kif::Win32::String &trackingId,
   Kif::Win32::String &outSubmissionId)
{
   // prepare result
   Kif::Win32::Diagnostics::Result *result = &dbConnection.LastResult;
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionRegistryGetSubmissionId (?,?,?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   long   rval = 0;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   cmd.Parameters.Add(_T("@SessionId"),sessionId.GetBuffer(),24);
   cmd.Parameters.Add(_T("@TrackingId"),trackingId,40);

   TCHAR subId[Submission::SubmissionIdLength+1];
   subId[0] = NULL;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pOutSubmissionId =
      cmd.Parameters.Add(_T("@OutSubmissionId"),subId, 20);
   pOutSubmissionId->Direction = Kif::Win32::MemoryParamDirection_Output;

   bool done = cmd.ExecuteNonQuery();
   rval = done ? pReturnValue->AsLong() : SubmissionError_DatabaseRequestFailed;
   if (rval != 0)
   {
      GetErrorMessage(rval,_T("Submission::GetSubmissionId"),*result);
   }
   else
   {
      wchar_t *refId = pOutSubmissionId->AsWideString();
      outSubmissionId.Copy(refId);
      outSubmissionId.Trim();

      if (outSubmissionId.GetLength() == 0)
         result->LocalErrorNo = SubmissionError_FailedRetrievingSubmissionId;
   }

   // dipose of resources
   cmd.Dispose();

   return(result);
}  // end of RegisterSubmission

/// <summary>Insert a new submission.</summary>
/// <param name="connection">database connection</param>
/// <param name="trackingId">Tracking id</param>
/// <param name="referenceId">Reference id</param>
/// <param name="controlId">Control id</param>
/// <param name="controlReferenceId">Control Reference id</param>
/// <param name="submittedFileName">Submssion file name</param>
/// <param name="sizeInBytes">size in bytes of the Submission file</param>
/// <param name="typeNo">Type number of the submission</param>
/// <param name="binaryData">Binary data of submission</param>
/// <param name="binaryDataSizeInBytes">size in bytes of the binary
/// data of submission</param>
/// <param name="outSubmissionId">Outputed Submission Id</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::Result *Submission::RegisterSubmission(
   SubmissionDbConnection &dbConnection,
   Kif::Win32::String &sessionId,
   Kif::Win32::String &trackingId,
   Kif::Win32::String &referenceId,
   Kif::Win32::String &controlId,
   Kif::Win32::String &controlReferenceId,
   Kif::Win32::String &sourceId,
   Kif::Win32::String &destinationId,
   Kif::Win32::DateTime *submissionDate,
   Kif::Win32::String &submittedFileName,
   DWORD sizeInBytes,
   short typeNo,
   short contentTypeNo,
   BYTE *binaryData,
   DWORD binaryDataSizeInBytes,
   Kif::Win32::String &outSubmissionId,
   TCHAR searchResultsFindings)
{
   // prepare result
   Kif::Win32::Diagnostics::Result *result = &dbConnection.LastResult;
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionRegistryInsertRecord (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   // prepare the "SearchResultsFidings" value / field...
   long   rval = 0;
   TCHAR srfVal[2];
   srfVal[0] = searchResultsFindings;
   srfVal[1] = 0;
   Kif::Win32::String srf(srfVal);

   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   cmd.Parameters.Add(_T("@SessionId"),sessionId.GetBuffer(),24);
   cmd.Parameters.Add(_T("@TrackingId"),trackingId,40);
   cmd.Parameters.Add(_T("@ReferenceId"),referenceId,20);
   cmd.Parameters.Add(_T("@ControlId"),controlId,40);
   cmd.Parameters.Add(_T("@ControlReferenceId"),controlReferenceId,40);
   cmd.Parameters.Add(_T("@SourceId"),sourceId,20);
   cmd.Parameters.Add(_T("@DestinationId"),destinationId,20);
   cmd.Parameters.Add(_T("@SubmissionDate"),submissionDate);
   cmd.Parameters.Add(_T("@SubmittedFileName"),submittedFileName,128);
   cmd.Parameters.Add(_T("@SizeInBytes"),(long)sizeInBytes);
   cmd.Parameters.Add(_T("@TypeNo"),typeNo);
   cmd.Parameters.Add(_T("@ContentTypeNo"),contentTypeNo);
   cmd.Parameters.Add(_T("@SearchResultsFindings"),srfVal,20);

   TCHAR subId[Submission::SubmissionIdLength+1];
   subId[0] = NULL;

   cmd.Parameters.Add(_T("@BinaryData"),binaryData,binaryDataSizeInBytes);

   Kif::Win32::Data::OdbcClient::OdbcParameter *pOutSubmissionId =
      cmd.Parameters.Add(_T("@OutSubmissionId"),subId, 20);
   pOutSubmissionId->Direction = Kif::Win32::MemoryParamDirection_Output;

   bool done = cmd.ExecuteNonQuery();
   rval = done ? pReturnValue->AsLong() : SubmissionError_DatabaseRequestFailed;
   if (rval != 0)
   {
      GetErrorMessage(rval,_T("Submission::RegisterSubmission"),*result);
   }
   else
   {
      wchar_t *refId = pOutSubmissionId->AsWideString();
      outSubmissionId.Copy(refId);
      outSubmissionId.Trim();
   }

   // dipose of resources
   cmd.Dispose();

   // TODO: output parameter issue
   // for some reason the output parameter from last call don't return the
   // requested "SubmissionId"! so let's make sure that we get it...

   // make sure that we did got a submission id...
   if (result->Success())
   {
      if (outSubmissionId.GetLength() == 0)
      {
         GetSubmissionId(dbConnection,sessionId,trackingId,outSubmissionId);
         if ((outSubmissionId.GetLength() == 0) && (result->Success()))
            result->LocalErrorNo = SubmissionError_FailedRetrievingSubmissionId;
      }
   }

   return(result);
}  // end of RegisterSubmission

/// <summary>Insert a new submission.</summary>
/// <param name="connection">database connection</param>
/// <param name="submissionId">submission id</param>
/// <param name="mediaId">media id</param>
/// <param name="formatNo">media format no.</param>
/// <param name="typeNo">media type no.</param>
/// <param name="sourceAgency">source agency</param>
/// <param name="captureDate">captured date</param>
/// <param name="metaData">media metadata (if any)</param>
/// <param name="binaryData">Binary data of media</param>
/// <param name="binaryDataSizeInBytes">size in bytes of the binary
/// data of media</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::Result *Submission::RegisterMedia(
   SubmissionDbConnection &dbConnection,
   Kif::Win32::String &sessionId,
   Kif::Win32::String &submissionId,
   Kif::Win32::String &mediaId,
   short formatNo,
   short typeNo,
   TCHAR *submitterId,
   Kif::Win32::DateTime *captureDate,
   Kif::Win32::String &metaData,
   BYTE *binaryData,
   DWORD binaryDataSizeInBytes)
{
   // prepare result
   Kif::Win32::Diagnostics::Result *result = &dbConnection.LastResult;
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionMediaRegistryInsertRecord (?,?,?,?,?,?,?,?,?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   long   rval = 0;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   cmd.Parameters.Add(_T("@SessionId"),sessionId.GetBuffer(),24);
   cmd.Parameters.Add(_T("@SubmissionId"),submissionId.GetBuffer(),20);
   cmd.Parameters.Add(_T("@MediaId"),mediaId.GetBuffer(),20);
   cmd.Parameters.Add(_T("@FormatNo"),formatNo);
   cmd.Parameters.Add(_T("@TypeNo"),typeNo);
   cmd.Parameters.Add(_T("@SubmitterId"),submitterId,20);
   cmd.Parameters.Add(_T("@CaptureDate"),captureDate);
   cmd.Parameters.Add(_T("@MetaData"),metaData.GetBuffer(),(DWORD)metaData.GetLength());
   cmd.Parameters.Add(_T("@BinaryData"),binaryData,binaryDataSizeInBytes);

   bool done = cmd.ExecuteNonQuery();
   rval = done ? pReturnValue->AsLong() : SubmissionError_DatabaseRequestFailed;
   if ((rval != 0) && (result != NULL))
   {
      GetErrorMessage(rval,_T("Submission::RegisterMedia"),*result);
   }

   // dipose of resources
   cmd.Dispose();

   return(result);
}  // end of RegisterMedia

/// <summary>Insert a new key id for a given submission.</summary>
/// <param name="connection">database connection</param>
/// <param name="submissionId">submission id</param>
/// <param name="keyType">key type</param>
/// <param name="keyId">key Id</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::Result *Submission::RegisterKeyId(
   SubmissionDbConnection &dbConnection,
   Kif::Win32::String &sessionId,
   Kif::Win32::String &submissionId,
   Kif::Win32::DataObjects::SubmissionKeyType keyType,
   TCHAR *keyId)
{
   // prepare result
   Kif::Win32::Diagnostics::Result *result = &dbConnection.LastResult;
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionKeyIdInsertRecord (?,?,?,?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   long   rval = 0;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   cmd.Parameters.Add(_T("@SessionId"),sessionId.GetBuffer(),24);
   cmd.Parameters.Add(_T("@SubmissionId"),submissionId.GetBuffer(),20);
   cmd.Parameters.Add(_T("@KeyTypeNo"),keyType);
   cmd.Parameters.Add(_T("@KeyId"),keyId, 20);

   bool done = cmd.ExecuteNonQuery();
   rval = done ? pReturnValue->AsLong() : SubmissionError_DatabaseRequestFailed;
   if ((rval != 0) && (result != NULL))
   {
      GetErrorMessage(rval,_T("Submission::RegisterKeyId"),*result);
   }

   // dipose of resources
   cmd.Dispose();

   return(result);
}  // end of RegisterKeyId

/// <summary>Update submission status.</summary>
/// <param name="connection">database connection</param>
/// <param name="submissionId">submission id</param>
/// <param name="statusNo">status number</param>
/// <param name="messageTypeNo">message type number</param>
/// <param name="xmlMessages">xml messages</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::Result *Submission::UpdateSubmissionStatus(
   SubmissionDbConnection &dbConnection,
   Kif::Win32::String &sessionId,
   Kif::Win32::String &submissionId,
   short statusNo,
   short messageTypeNo,
   Kif::Win32::String &xmlMessages)
{
   // prepare result
   Kif::Win32::Diagnostics::Result *result = &dbConnection.LastResult;
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionRegistryUpdateStatus (?,?,?,?,?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   long   rval = 0;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   cmd.Parameters.Add(_T("@SessionId"),sessionId.GetBuffer(),24);
   cmd.Parameters.Add(_T("@SubmissionId"),submissionId.GetBuffer(),20);
   cmd.Parameters.Add(_T("@StatusNo"),statusNo);
   cmd.Parameters.Add(_T("@MessageTypeNo"),messageTypeNo);
   cmd.Parameters.Add(_T("@XmlMessages"),
      xmlMessages.GetBuffer(), (DWORD)xmlMessages.GetLength());

   bool done = cmd.ExecuteNonQuery();
   rval = done ? pReturnValue->AsLong() : SubmissionError_DatabaseRequestFailed;
   if ((rval != 0) && (result != NULL))
   {
      GetErrorMessage(rval,_T("Submission::RegisterMedia"),*result);
   }

   // dipose of resources
   cmd.Dispose();

   return(result);
}  // end of RegisterMedia

/// <summary>Update submission status.</summary>
/// <param name="connection">database connection</param>
/// <param name="submissionId">submission id</param>
/// <param name="referenceId1">application dependent reference-id 1</param>
/// <param name="referenceId2">application dependent reference-id 2</param>
/// <param name="referenceId3">application dependent reference-id 3</param>
/// <param name="statusNo">status number</param>
/// <param name="optionNo">option number</param>
/// <param name="outReferenceId">
/// application dependent output reference id</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::Result *Submission::SubmissionQueueUpdate(
   SubmissionDbConnection &dbConnection,
   Kif::Win32::String &sessionId,
   Kif::Win32::String &submissionId,
   Kif::Win32::String &referenceId1,
   Kif::Win32::String &referenceId2,
   Kif::Win32::String &referenceId3,
   short statusNo,
   short optionNo,
   Kif::Win32::String &outReferenceId)
{
   // prepare result
   Kif::Win32::Diagnostics::Result *result = &dbConnection.LastResult;
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionQueueEntry (?,?,?,?,?,?,?,?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   long   rval = 0;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   cmd.Parameters.Add(_T("@SessionId"),sessionId.GetBuffer(),24);
   cmd.Parameters.Add(_T("@SubmissionId"),submissionId.GetBuffer(),20);

   cmd.Parameters.Add(_T("@ReferenceId1"),referenceId1.GetBuffer(),20);
   cmd.Parameters.Add(_T("@ReferenceId2"),referenceId2.GetBuffer(),20);
   cmd.Parameters.Add(_T("@ReferenceId3"),referenceId3.GetBuffer(),20);

   cmd.Parameters.Add(_T("@StatusNo"),statusNo);
   cmd.Parameters.Add(_T("@Option"),optionNo);

   TCHAR refId[] = _T("                    ");
   Kif::Win32::Data::OdbcClient::OdbcParameter *pOutReferenceId =
      cmd.Parameters.Add(_T("@OutReferenceId"),refId, 20);
   pOutReferenceId->Direction = Kif::Win32::MemoryParamDirection_Output;

   bool done = cmd.ExecuteNonQuery();
   rval = done ? pReturnValue->AsLong() : SubmissionError_DatabaseRequestFailed;
   if ((rval != 0) && (result != NULL))
   {
      GetErrorMessage(rval,_T("Submission::SubmissionQueueUpdate"),*result);
   }

   // dipose of resources
   cmd.Dispose();

   return(result);
}  // end of RegisterMedia

/// <summary>Get Submission File Data</summary>
/// <param name="connection">database connection</param>
/// <param name="submissionId">submission id</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::ResultObject<Kif::Win32::MemoryBuffer<BYTE>>
  *Submission::SubmissionGetFileData(
      SubmissionDbConnection &dbConnection,
      Kif::Win32::String &sessionId,
      Kif::Win32::String &submissionId,
      Kif::Win32::String &transactionControlNumber)
{
   // prepare result
   Kif::Win32::Diagnostics::
      ResultObject<Kif::Win32::MemoryBuffer<BYTE>> *result =
         new Kif::Win32::Diagnostics::
            ResultObject<Kif::Win32::MemoryBuffer<BYTE>>();
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionGetFileData (?,?,?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   long rval = 0;
   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   cmd.Parameters.Add(_T("@SessionId"),sessionId.GetBuffer(),24);
   cmd.Parameters.Add(_T("@SubmissionId"),submissionId.GetBuffer(),20);
   cmd.Parameters.Add(_T("@TransactionControlNumber"),
      transactionControlNumber.GetBuffer(),40);

   BYTE *imageBytes = (BYTE*)Kif::Win32::Memory::alloc(MAX_IMAGE_SIZE);
   imageBytes[0] = NULL;

   TCHAR *estr = _T("");
   Kif::Win32::Data::OdbcClient::OdbcParameter *c1,*c2;
   c1 = cmd.Columns.Add(_T("OriginalFilePath"),estr,128);
   c2 = cmd.Columns.Add(_T("BinaryData"),imageBytes,MAX_IMAGE_SIZE);

   Kif::Win32::Data::OdbcClient::OdbcDataReader *reader = cmd.OpenReader();
   if ((reader == NULL) && (result != NULL))
   {
      result->LocalErrorNo = -1;
   }
   else
   if (reader->Read())
   {
      TCHAR *filePath = c1->AsWideString();
      // TODO: fetch actual image size...
      Kif::Win32::MemoryBuffer<BYTE> *buff = result->GetObjectPointer();
      buff->Copy((BYTE*)c2->GetBuffer(), c2->GetSizeInBytes());
   }

   if (reader != NULL)
      delete reader;

   // dipose of resources
   cmd.Dispose();

   return(result);
}  // end of SubmissionGetFileData

/// <summary>Validate submission entry.</summary>
/// <param name="connection">database connection</param>
/// <param name="submissionId">submission id</param>
/// <param name="referenceId">returned referenceId ("" if none)</param>
/// <param name="statusNo">return status number</param>
/// <returns>a pointer to a Result is returned. the caller should not dispose
/// the result since it is a pointer to the "LastResult" in the connection
/// object</returns>

Kif::Win32::Diagnostics::Result *Submission::SubmissionValidateEntry(
   SubmissionDbConnection &dbConnection,
   Kif::Win32::String &sessionId,
   Kif::Win32::String &submissionId,
   Kif::Win32::String &referenceId,
   short *statusNo)
{
   // prepare result
   Kif::Win32::Diagnostics::Result *result = &dbConnection.LastResult;
   result->Clear();

   // prepare database command
   Kif::Win32::Data::OdbcClient::OdbcCommand cmd(
      &dbConnection.Connection,
      _T("{ ?= CALL SubmissionValidateEntry (?,?,?,?) }"),
      Kif::Win32::Data::Command_StoredProcedure);

   TCHAR  outRefId[] = _T("01234567890123456789");
   long   rval = 0;

   Kif::Win32::Data::OdbcClient::OdbcParameter *pReturnValue =
      cmd.Parameters.Add(_T("@RETURN_VALUE"),rval);
   pReturnValue->Direction = Kif::Win32::MemoryParamDirection_Output;

   cmd.Parameters.Add(_T("@SessionId"),sessionId.GetBuffer(),24);
   cmd.Parameters.Add(_T("@SubmissionId"),submissionId.GetBuffer(),20);

   short stsNo = 0;
   Kif::Win32::MemoryDataItem *p1,*p2;
   p1 = cmd.Parameters.Add(_T("@OutReferenceId"),outRefId,SizeOf(outRefId));
   p2 = cmd.Parameters.Add(_T("@OutStatusNo"),stsNo);
   p1->Direction = Kif::Win32::MemoryParamDirection_InputOutput;
   p2->Direction = Kif::Win32::MemoryParamDirection_InputOutput;

   bool done = cmd.ExecuteNonQuery();
   rval = done ? pReturnValue->AsLong() : SubmissionError_DatabaseRequestFailed;
   if ((rval < 0) && (result != NULL))
   {
      if (rval <= SubmissionError_InvalidIncidentId)
         rval = SubmissionError_InvalidIncidentId;
      referenceId.Copy(Kif::Win32::String::GetEmptyString());
      if (statusNo != NULL)
         *statusNo = -1;
      GetErrorMessage(rval,_T("Submission::ValidateEntry"),*result);
   }
   else
   {
      wchar_t *refId = p1->AsWideString();
      referenceId.Copy(refId);
      referenceId.Trim();
      if (statusNo != NULL)
         *statusNo = (short)p2->AsLong();
   }

   // dipose of resources
   cmd.Dispose();

   return(result);
}  // end of RegisterMedia

/// <summary>Get the entry submision status message based on a given
/// status number returned by the "SubmissionValidateEntry" procedure</summary>
/// <param name="statusNo">database connection</param>
/// <returns>a pointer a string message is returned. Note that Caller is
/// responsible for disposing the string</returns>

Kif::Win32::String *Submission::GetEntrySubmissionStatusMessage(short statusNo)
{
Kif::Win32::String *errStr = new Kif::Win32::String();

TCHAR err11[] =
   _T("One exact match PersonStateId / ReferenceId record was found");
TCHAR err10[] =
   _T("One match (not exact) PersonStateId / ReferenceId record was found");
TCHAR err99[] = _T("Server error while processing SQL request");
TCHAR err2[]  =
   _T("Several matching PersonStateId / ReferenceId records found or Incident not found");
TCHAR err1[]  = _T("Incident + PersonStateId / ReferenceId not found or invalid");
TCHAR err15274[] =
   _T("Access to the remote server is denied because the current ")
   _T("security context is not trusted");

   if (statusNo == -15274)
      errStr->Copy(err15274,SizeOf(err15274));
   else
   if (statusNo == -99)
      errStr->Copy(err99,SizeOf(err99));
   else
   if (statusNo == -2)
      errStr->Copy(err2,SizeOf(err2));
   else
   if (statusNo == -1)
      errStr->Copy(err1,SizeOf(err1));
   else
   if (statusNo == 10)
      errStr->Copy(err10,SizeOf(err10));
   else
   if (statusNo == 11)
      errStr->Copy(err10,SizeOf(err10));

   return(errStr);
}  // end of GetEntrySubmissionStatusMessage

}  // end of Kif::Win32::DataObjects
}  // end of Kif::Win32
}  // end of Kif

